package product;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class ProStock {
	String cod;
	int tot;
	Connection con;
	Date nal;
	String dept;
	void DbCon()throws Exception{
		 Class.forName("oracle.jdbc.driver.OracleDriver");
		 System.out.println("드라이버 로딩 성공...");
	      String url="jdbc:oracle:thin:@192.168.10.4:1521:XE";
	        
	      String user="hr";
	      String pwd="hr";
	        
	     con=DriverManager.getConnection(url,user,pwd);
	      System.out.println("DB 연결 성공!");
	}
	public void add(String code,int st_tot) throws Exception{//생산할때 해당코드,생산량
		try{
		DbCon();
		Statement st=con.createStatement();
		String sql=" insert into stock values('"+code+"',"+st_tot+",sysdate)";
		ResultSet rs=st.executeQuery(sql);
		
	
		rs.close();
		
		st.close();
		con.close();
		}
		catch(Exception ex){
//			DbCon();
//			System.out.println("Error!!");
			Statement st=con.createStatement();
			String sql2="select s_code from stock";
			ResultSet rs2=st.executeQuery(sql2);
			while(rs2.next()){
				String dept=rs2.getString(1);
				if(code.equals(dept)){
					String sql4="update stock set s_date=sysdate,s_amount=s_amount +"+st_tot+" where s_code= '"+code+"'";
					ResultSet rs4=st.executeQuery(sql4);
				}
			}
		
		}
	}

	public void update(String code,int pre_ea,int after_ea) throws Exception{
		DbCon();
		
		int a = pre_ea;
		int b = after_ea;
		String co = code;
		String sql="update stock set s_date=sysdate,s_amount=(s_amount+(?-?)) where s_code=?";
		PreparedStatement pstmt=con.prepareStatement(sql);	
		pstmt.setInt(1, b);
		pstmt.setInt(2, a);
		pstmt.setString(3, co);
		
		pstmt.executeUpdate();
	
		pstmt.close();
		con.close();
	} 
	public void delete(String code,int ea)throws Exception{//삭제 코드,삭제할 수량
		DbCon();
		Statement st=con.createStatement();
		
		String sql="update stock set s_date=sysdate,s_amount=s_amount-"+ea+" where s_code= '"+code+"'";
		ResultSet rs=st.executeQuery(sql);
		
		
		rs.close();
	
		st.close();
		con.close();
	}
	public void recall(String code,int ea)throws Exception{
		DbCon();
		Statement st=con.createStatement();
		

		String sql="update stock set s_date=sysdate,s_amount=s_amount-"+ea+" where s_code= '"+code+"'";
		ResultSet rs=st.executeQuery(sql);
	
		rs.close();
		st.close();
		con.close();
	}
}
